package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.SuShe;
import model.WeiGui;
import utils.DBconn;

public class SuSheDao {
	//数据库连接
		// 获取总行数——针对宿管
		    public int getSGCount(String build) {
		    	 int count = 0;
		    	 String buildvalue=build+"%";
		    	 String sql = "SELECT COUNT(*) FROM sushe WHERE roomid LIKE ?";  // 假设表名为 users
		        try (Connection conn = DBconn.getConnection();
		                PreparedStatement stmt = conn.prepareStatement(sql)) {
		           		stmt.setString(1, buildvalue);
		           		ResultSet rs = stmt.executeQuery();
		               if (rs.next()) {
		            	   count = rs.getInt(1);
		            	   //测试行
		            	   System.out.println("宿管看到的的行数是: " + count);
		            }
		        } catch (SQLException e) {
		            e.printStackTrace();
		        }
		        return count;
		    }
		// 获取指定页数据——针对宿管
		    public List<SuShe> getSSSGPage(String build, int currentPage, int pageSize) {
		        List<SuShe> wgs = new ArrayList<>();
		        String buildvalue=build+"%";
		        String sql = "SELECT * FROM sushe WHERE roomid LIKE ? LIMIT ?, ?";
		        try (Connection conn = DBconn.getConnection();
		             PreparedStatement stmt = conn.prepareStatement(sql)) {
		        	stmt.setString(1, buildvalue);
		            stmt.setInt(2, (currentPage - 1) * pageSize);  // 计算偏移量
		            stmt.setInt(3, pageSize);  // 设置每页显示的记录数
		            ResultSet rs = stmt.executeQuery();
		            while (rs.next()) {
		            	SuShe wg = new SuShe();
		            	wg.setStudentId(rs.getString("id"));
		            	wg.setRoomid(rs.getString("roomid"));
		            	wg.setStudentClass(rs.getString("class"));
		            	wg.setStudentName(rs.getString("name"));
		            	wg.setQuota(rs.getString("quota"));
		            	wg.setGuige(rs.getString("guige"));
		            	wgs.add(wg);
		            }
		            //测试行
		     	   System.out.println("到达getWGSGPage，build："+build);
		   
		        } catch (SQLException e) {
		            e.printStackTrace();
		        }
		        return wgs;
		    }

		//******************************************辅导员功能类**************************************
		// 获取总行数——针对老师
		    public int getSSFDYCount(List<String> idclass) {
		    	
		        int count = 0;
		        String sql = "SELECT COUNT(*) " +buildSqlQuery(idclass);
		        //String sql = "SELECT COUNT(*) FROM weigui WHERE id = ?";  // 假设表名为 users
		        //测试行
		        System.out.println("getFDYCount:"+sql);
		        try (Connection conn = DBconn.getConnection();
		                PreparedStatement stmt = conn.prepareStatement(sql)) {
		        		//循环输入class值
		        		for(int i=1;i<idclass.size();i++) {
		        			stmt.setString(i, idclass.get(i));
		        		//测试行，康康循环了几次
		        		System.out.println("getFDYCount循环了："+i);
		        		}
		        		
		           		//查询总行数
		        		ResultSet rs = stmt.executeQuery();
		        		if (rs.next()) {
		            	   count = rs.getInt(1);
		        		}
		        } catch (SQLException e) {
		            e.printStackTrace();
		        }
		        return count;
		    }
		 // 获取指定页数据——针对老师
		    public List<SuShe> getSSFDYPage(List<String> idclass, int currentPage, int pageSize) {
		    	//for循环是在测试这个idclass的会话到底有无存入班级
		    	for (int i = 0; i < idclass.size(); i++) {
		    	    System.out.println("getWGFDYPage:"+idclass.get(i));
		    	}
		    	List<SuShe> wgs = new ArrayList<>();
		        String sql = "SELECT sushe.* " +buildSqlQuery(idclass)+"LIMIT ?, ?;";
		        System.out.println("getWGFDYPage:"+sql);
		        try (Connection conn = DBconn.getConnection();
		             PreparedStatement stmt = conn.prepareStatement(sql)) {
		        	//循环输入class值
		        	int i;
		        	for(i=1;i<idclass.size();i++) {
		        		stmt.setString(i, idclass.get(i));
		        	}
		        	
		            stmt.setInt(i, (currentPage - 1) * pageSize);  // 计算偏移量
		            stmt.setInt(i+1, pageSize);  // 设置每页显示的记录数
		            ResultSet rs = stmt.executeQuery();
		            while (rs.next()) {
		            	SuShe wg = new SuShe();
		            	wg.setStudentId(rs.getString("id"));
		            	wg.setRoomid(rs.getString("roomid"));
		            	wg.setStudentClass(rs.getString("class"));
		            	wg.setStudentName(rs.getString("name"));
		            	wg.setQuota(rs.getString("quota"));
		            	wg.setGuige(rs.getString("guige"));
		            	wgs.add(wg);
		            }
		        } catch (SQLException e) {
		            e.printStackTrace();
		        }
		        return wgs;
		    }
		 //需要针对老师的班级动态去建一个查询语句
		    public String buildSqlQuery(List<String> classArray) {
		        // 构建 IN 子句的部分
		        StringBuilder inClass = new StringBuilder();
		        for (int i =1; i < classArray.size(); i++) {
		        	inClass.append("?");
		            if (i < classArray.size() - 1) {
		            	inClass.append(", ");
		            }
		        }

		        // 完整的 SQL 查询语句
		        String sql = 
		                     "FROM sushe WHERE class IN (" + inClass.toString() + ") ";
		        //测试行：查看输出的sql语句
		        System.out.println("buildSqlQuery:"+sql);

		        return sql;
		    }

		  //*******************************************学生用***********************************************
		  	/*
		  	 * 功能描述：1、学生的宿舍信息
		  	 * */
		  	public List<SuShe> findSameRoomSuSheData(String studentId) {
		  		List<SuShe> Messages = new ArrayList<>();
		  		Connection conn = null;
		  		PreparedStatement stmt = null;
		  		ResultSet rs = null;
		  							    
		  		try { 	
		  			conn = DBconn.getConnection();
		  			//获取此id的所有信息
		  			String sql = "SELECT * FROM sushe WHERE roomid =(SELECT roomid FROM sushe WHERE id = ?) ";
		  			stmt = conn.prepareStatement(sql);
		  			stmt.setString(1, studentId);
		  			rs = stmt.executeQuery();			        						        		        						     
		  							        
		  			while (rs.next()) {						        	
		  				SuShe lm = new SuShe();
		  							
		  				lm.setRoomid(rs.getString("roomid"));
		  				lm.setStudentId(rs.getString("id")); 
		  				lm.setStudentName(rs.getString("name"));
		  				lm.setQuota(rs.getString("quota"));
		  				lm.setGuige(rs.getString("guige"));
		  				lm.setStudentClass(rs.getString("class"));
		  				
		  							            
		  				Messages.add(lm);
		  			}
		  		}catch (SQLException e) {
		  				System.out.print("获取数据库连接失败了");
		  				e.printStackTrace();
		  		}finally {
		  				try { if (rs != null) rs.close(); } catch (SQLException e) { /* ignored */ }
		  				try { if (stmt != null) stmt.close(); } catch (SQLException e) { /* ignored */ }
		  				try { if (conn != null) conn.close(); } catch (SQLException e) { /* ignored */ }	        
		  		}						    
		  				return Messages;
		  	}

		  	//*******************************************物业用***********************************************
		  	/*
		  	 * 功能描述：1、物业查询所有学生的宿舍信息
		  	 * */
		  	public List<SuShe> findParteRoomSuSheData() {
		  		List<SuShe> Messages = new ArrayList<>();
		  		Connection conn = null;
		  		PreparedStatement stmt = null;
		  		ResultSet rs = null;
		  							    
		  		try { 	
		  			conn = DBconn.getConnection();
		  			
		  			String sql = "SELECT roomid, MAX(quota) AS quota, MAX(guige) AS guige FROM sushe GROUP BY roomid";
		  			stmt = conn.prepareStatement(sql);
		  			rs = stmt.executeQuery();			        						        		        						     
		  							        
		  			while (rs.next()) {						        	
		  				SuShe lm = new SuShe();
		  				
		  				lm.setRoomid(rs.getString("roomid"));
		  				lm.setQuota(rs.getString("quota"));
		  				lm.setGuige(rs.getString("guige"));
		  						            
		  				Messages.add(lm);
		  			}
		  		}catch (SQLException e) {
		  				System.out.print("获取数据库连接失败了");
		  				e.printStackTrace();
		  		}finally {
		  				try { if (rs != null) rs.close(); } catch (SQLException e) { /* ignored */ }
		  				try { if (stmt != null) stmt.close(); } catch (SQLException e) { /* ignored */ }
		  				try { if (conn != null) conn.close(); } catch (SQLException e) { /* ignored */ }	        
		  		}						    
		  				return Messages;
		  	}
		  	
		  	/*
		  	 * 功能描述：2、物业修改宿舍配置信息
		  	 * */
		  	
		  	public boolean WyUpdateleave(String number,String quota,String guige) {
		  		boolean b = false; // 初始化返回值为 false
		  	    Connection conn = null;
		  	    PreparedStatement stmt = null;
		  	    try {
		  	    			    	
		  	    	
		  	        // 获取数据库连接
		  	        conn = DBconn.getConnection();
		  	        // 编写 SQL 插入语句
		  	        String sql = "UPDATE sushe SET quota = ?, guige = ? WHERE roomid = ?";
		  	        stmt = conn.prepareStatement(sql);
		  	        
		  	        // 设置参数值
		  	        
		  		    stmt.setString(1, quota); 
		  		    stmt.setString(2, guige); 
		  		    stmt.setString(3, number);
		  	        
		  	        // 执行修改操作
		  	        int rowsAffected = stmt.executeUpdate();
		  	        // 如果受影响的行数大于 0，则设置返回值为 true
		  	        if (rowsAffected > 0) {
		  	            b = true;
		  	        }
		  	    } catch (SQLException e) {
		  	        e.printStackTrace();
		  	    } finally {
		  	        // 关闭资源
		  	        try {
		  	            if (stmt != null) stmt.close();
		  	            if (conn != null) conn.close();
		  	        } catch (SQLException ex) {
		  	            ex.printStackTrace();
		  	        }
		  	    }
		  	    return b; // 返回操作结果
		  	     
		  	}
		  	
		  	//*******************************************管理员用***********************************************
		  	/*
		  	 * 功能描述：2、管理员查询宿舍入住信息
		  	 * */
		  	
		  	public List<SuShe> findAlleRoomSuSheData() {
		  		List<SuShe> Messages = new ArrayList<>();
		  		Connection conn = null;
		  		PreparedStatement stmt = null;
		  		ResultSet rs = null;
		  							    
		  		try { 	
		  			conn = DBconn.getConnection();
		  			
		  			String sql = "SELECT * FROM sushe";
		  			stmt = conn.prepareStatement(sql);
		  			rs = stmt.executeQuery();			        						        		        						     
		  							        
		  			while (rs.next()) {						        	
		  				SuShe lm = new SuShe();
		  				
		  				
		  				lm.setRoomid(rs.getString("roomid"));
		  				lm.setStudentId(rs.getString("id")); 
		  				lm.setStudentName(rs.getString("name"));
		  				lm.setQuota(rs.getString("quota"));
		  				lm.setGuige(rs.getString("guige"));
		  				lm.setStudentClass(rs.getString("class"));
		  						            
		  				Messages.add(lm);
		  			}
		  		}catch (SQLException e) {
		  				System.out.print("获取数据库连接失败了");
		  				e.printStackTrace();
		  		}finally {
		  				try { if (rs != null) rs.close(); } catch (SQLException e) { /* ignored */ }
		  				try { if (stmt != null) stmt.close(); } catch (SQLException e) { /* ignored */ }
		  				try { if (conn != null) conn.close(); } catch (SQLException e) { /* ignored */ }	        
		  		}						    
		  				return Messages;
		  	}
		  	
		  	

/*
* 功能描述：2、管理员修改宿舍入住信息
* */
public boolean daoAdminUpdateleave(String stuid,String stuname,String classroom,String oldroomid,String oldid) {
boolean b = false; // 初始化返回值为 false
Connection conn = null;
PreparedStatement stmt = null;
try { 
System.out.print("数据都有吗"+stuid+stuname+stuname+classroom+"旧宿舍号"+oldroomid+"旧学号"+oldid+"没了");
// 获取数据库连接
conn = DBconn.getConnection();
// 编写 SQL 插入语句
String sql = "UPDATE sushe SET id = ?, name = ?, class = ? WHERE roomid = ? AND id = ?";
stmt = conn.prepareStatement(sql);
// 设置参数值
stmt.setString(1, stuid); 
stmt.setString(2, stuname); 
stmt.setString(3, classroom);
stmt.setString(4, oldroomid);
stmt.setString(5, oldid);
// 执行修改操作
int rowsAffected = stmt.executeUpdate();
// 如果受影响的行数大于 0，则设置返回值为 true
if (rowsAffected > 0) {
b = true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭资源
try {
if (stmt != null) stmt.close();
if (conn != null) conn.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
return b; // 返回操作结果
}
		  	
		  

}
